<?php
/**
 * 商城前端用
 * User: lixy
 * Date: 2016/9/19
 * Time: 13:32
 */

namespace app\models;


use app\librarys\DbCacheUtility;
use app\librarys\Inteface;
use Yii;
use yii\helpers\Url;

class MallFrontEnd
{
    public static function getCacheDuration()
    {
        return 60;  // 缓存时间60秒
    }

    //is_enabled=1 and is_shelved=1 and is_show=1  order by is_top desc,shelve_datetime desc;
    /**
     * 根据identifierCode获取机构ad
     * @param $institutionCode
     * @param $identifierCode
     * @return mixed
     */
    public static function getAdByIdentifier($institutionCode, $identifierCode)
    {
        $cmd = Yii::$app->db->createCommand('
            SELECT t0.*, t1.name AS category_name FROM t_ad t0
            LEFT JOIN t_ad_category t1 ON t1.id=t0.category_id
            WHERE t0.institution_code=:institutionCode AND t1.identifier_code=:identifierCode and t0.is_shown=1
            ORDER BY t0.name');
        $cmd->bindValue(':institutionCode', $institutionCode);
        $cmd->bindValue(':identifierCode', $identifierCode);
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }

    /**
     * 查询商品扩展分类列表
     * @param string $institution_code
     * @return array|null
     */
    public static function getGoodsExtendCategoryList($institution_code)
    {
        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "select * from t_goods_extend_category where institution_code=:institution_code and is_enabled=1 order by  modify_datetime desc";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = DbCacheUtility::queryAll($cmd, self::getCacheDuration());
            return $result;
        }

        return $result;
    }

    /**获取热销产品
     * @param $institution_code
     * @return mixed
     */
    public static function getHotGoodsList($institution_code)
    {
        $sql = "select g.*,img.*,g.selling_price*t5.integral_rate as selling_integral, t5.integral_unit from t_goods g
      left join t_institution t5 on t5.`code`=g.institution_code
      left join (select *  from t_goods_image where `type`=1 ) img  on g.code=img.goods_code
        where g.institution_code=:institution_code and g.is_hot=1 and
        g.is_activity=0 and g.is_enabled=1 and g.is_shelved=1 and g.is_show=1
          order by g.is_top desc,g.shelve_datetime desc";
        $cmd = Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);

        //执行获取结果
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }

    /**
     * 获取历史足迹商品
     * @param $institution_code
     * @param $goods_codes
     * @return array
     */
    public static function getHisGoodsList($institution_code, $goods_codes)
    {
        $sql = "SELECT g.code,gi.image_url,g.name,i.integral_unit,g.is_crossborder,g.selling_price*i.integral_rate AS selling_integral
                FROM t_goods g
                  INNER JOIN t_goods_image gi ON gi.goods_code = g.code
                  INNER JOIN t_institution i ON i.code = g.institution_code
                WHERE gi.type = 1 
                  AND g.institution_code = :institution_code
                  AND g.code IN ($goods_codes)";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);

        $result = $cmd->queryAll();
        return $result;
    }

    /**获取产品1级分类
     * @param $institution_code
     * @return mixed
     */
    public static function getGoodsCategoryLv1($institution_code)
    {
        $sql = "select * from t_goods_category where is_show=1 and institution_code=:institution_code and length(code)=2 order by code";
        $cmd = Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }

    /**获取产品下一级分类
     * @param $institution_code
     * @param $parent_code
     * @return mixed
     */
    public static function getGoodsCategoryNextLv($institution_code, $parent_code)
    {
        $sql = "select * from t_goods_category where is_show=1 and institution_code=:institution_code and parent_code=:parent_code order by code";
        $cmd = Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':parent_code', $parent_code);
        //执行获取结果
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }


    /**前端商城获取商品列表
     * @param $institutionCode
     * @param null $goodsCode
     * @param null $goodsName
     * @param null $goodsCategoryCode
     * @param null $goodsExtendCategoryCode
     * @param null $isActivity
     * @param null $isNewest
     * @param null $isHot
     * @param null $integralForm
     * @param null $integralTo
     * @param null $sort
     * @param bool $getCount
     * @param int $page
     * @param int $pageSize
     * @return array|false|null|string
     */
    public static function getGoodsListBySearch($institutionCode, $goodsCode = null, $goodsName = null, $goodsCategoryCode = null, $goodsExtendCategoryCode = null, $isActivity = null, $isNewest = null, $isHot = null, $integralFrom = null, $integralTo = null, $sort = null
        , $getCount = true, $page = 1, $pageSize = 20)
    {
        if ($institutionCode == null || $institutionCode == '') {
            return '';
        }

        $filterSql = '';
        if ($goodsCode) {
            $filterSql = $filterSql . ' AND t1.code LIKE :goods_code ';
        }
        if ($goodsName) {
            $filterSql = $filterSql . ' AND t1.name LIKE :goods_name ';
        }
        $goodsCategorySql = '';
        if ($goodsCategoryCode) {
            $goodsCategorySql = ' LEFT JOIN t_goods_category_goods t2 ON t2.category_code=:goods_category_code AND t2.institution_code=t1.institution_code AND t2.goods_code=t1.code ';
            $filterSql = $filterSql . ' AND t2.category_code=:goods_category_code';
        }

        $goodsExtendCategorySql = '';
        if ($goodsExtendCategoryCode) {
            $goodsExtendCategorySql = ' LEFT JOIN t_goods_extend_category_goods t5 ON t5.extend_category_id=:extend_category_id AND t5.goods_code=t1.code
           LEFT JOIN (select goods_code,category_code from t_goods_category_goods where institution_code=:institution_code) t2 ON   t2.goods_code=t1.code ';
            $filterSql = $filterSql . ' AND t5.extend_category_id=:extend_category_id';
        }
        if ($isActivity != null) {
            $filterSql = $filterSql . ' AND t1.is_activity=:is_activity';
        }
        if ($isNewest != null) {
            $filterSql = $filterSql . ' AND t1.is_newest=:is_newest';
        }
        if ($isHot != null) {
            $filterSql = $filterSql . ' AND t1.is_hot=:is_hot';
        }
        if ($integralFrom != null) {
            $filterSql = $filterSql . ' AND (t1.selling_price*t3.integral_rate)>=:integral_f';
        }
        if ($integralTo != null) {
            $filterSql = $filterSql . ' AND (t1.selling_price*t3.integral_rate)<=:integral_t';
        }
        $orderSql = ' ORDER BY ';
        switch ($sort) {
            case 0:  // 默认排序
                if ($goodsExtendCategoryCode) {
                    $orderSql = $orderSql . ' t1.is_top DESC,t2.category_code DESC ';
                } else {
                    $orderSql = $orderSql . ' t1.is_top DESC ';
                }
                break;
            case 1:  // 按价格从小到大
                $orderSql = $orderSql . ' t1.selling_price ';
                break;
            case 2:  // 按价格从大到小
                $orderSql = $orderSql . ' t1.selling_price DESC ';
                break;
            case 3:  // 按上架时间从新到旧
                $orderSql = $orderSql . '  t1.shelve_datetime ';
                break;
            case 4:  // 按上架时间从旧到新
                $orderSql = $orderSql . ' t1.shelve_datetime DESC ';
                break;
        }
        //跨境商品排序
        $orderSql = $orderSql . ',t1.is_crossborder DESC ';

        $csql = "SELECT t1.`code`, t1.institution_code, t1.`name`, t1.model, t1.brand, t1.selling_price, t1.selling_price*t3.integral_rate AS selling_integral, t1.type, t1.bind_type, t1.is_newest, t1.is_hot, t1.is_top, t1.is_activity, t1.is_trusteeship,t1.is_crossborder
          , t4.image_url AS goods_image,t6.old_price*t3.integral_rate as old_price
            FROM (select *,IFNULL(group_code,code) as ext_group_code from t_goods
             WHERE  institution_code=:institution_code AND is_enabled=1  AND is_shelved=1 AND is_show=1  ) t1
             $goodsCategorySql $goodsExtendCategorySql
            LEFT JOIN t_institution t3 ON t3.`code`=t1.institution_code
            LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.`code` AND t4.type=1
            LEFT  JOIN  t_activity_bargain_goods t6 on t6.goods_code=t1.`code` and t6.institution_code= t1.institution_code
            WHERE 1=1 
            $filterSql
            group by t1.ext_group_code $orderSql";
        $sql = '';
        if ($getCount) {
            $sql = "select count(1) from ( $csql ) t99";
        } else {
            $sql = "select * from ( $csql ) t99   LIMIT :offset, :limit";
        }
        $cmd = \Yii::$app->db->createCommand($sql);

        if ($getCount == false) {
            $page = $page < 1 ? 1 : $page;
            $offset = ($page - 1) * $pageSize;
            $cmd->bindValue(':offset', $offset);
            $cmd->bindValue(':limit', $pageSize);
        }

        $cmd->bindValue(':institution_code', $institutionCode);

        if ($goodsCode) {
            $cmd->bindValue(':goods_code', '%' . $goodsCode . '%');
        }
        if ($goodsName) {
            $cmd->bindValue(':goods_name', '%' . $goodsName . '%');
        }
        if ($goodsCategoryCode) {
            $cmd->bindValue(':goods_category_code', $goodsCategoryCode);
        }
        if ($goodsExtendCategoryCode) {
            $cmd->bindValue(':extend_category_id', $goodsExtendCategoryCode);
        }
        if ($isActivity != null) {
            $cmd->bindValue(':is_activity', $isActivity);
        }
        if ($isNewest != null) {
            $cmd->bindValue(':is_newest', $isNewest);
        }
        if ($isHot != null) {
            $cmd->bindValue(':is_hot', $isHot);
        }
        if ($integralFrom != null) {
            $cmd->bindValue(':integral_f', $integralFrom);
        }
        if ($integralTo != null) {
            $cmd->bindValue(':integral_t', $integralTo);
        }
//        echo ($cmd->getRawSql());exit;
        if ($getCount) {
            return DbCacheUtility::queryScalar($cmd, self::getCacheDuration());
        } else {
            return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
        }
    }

    /**
     * 获取商品详情(仅包含商品用于前端商城所要展示的数据)
     * @param string $institutionCode
     * @param string $goodsCode
     * @return array|false|null
     */
    public static function getGoodsDetail($institutionCode, $goodsCode)
    {
        $sql = "
        SELECT t.`code`, t.`name`, t.model, t.format, t.brand,t.is_crossborder, t.selling_price, t.selling_price*t5.integral_rate as selling_integral, t5.integral_unit, t.type, t.bind_type, t7.detail_html, t.is_enabled, t.is_shelved,t.is_activity, t.is_show,t.group_code, t4.image_url as goods_image,c.code as category_code,c.name as category_name
        ,t6.old_price *t5.integral_rate as old_price
        FROM t_goods t
        left join t_goods_category_goods cg on cg.goods_code = t.code and cg.institution_code = t.institution_code
        left join t_goods_category c on c.code = cg.category_code and c.institution_code = cg.institution_code
        left join t_goods_image t4 on t4.goods_code=t.`code` and t4.type=1
        left join t_institution t5 on t5.`code`=t.institution_code
        left join t_activity_bargain_goods t6 on t6.goods_code=t.`code` and t6.institution_code= t.institution_code
        left join t_goods_detail_html t7 on t7.`code`=t.`code` and t7.institution_code=t.institution_code
        where t.institution_code=:institution_code and t.code=:goods_code ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':goods_code', $goodsCode);
        $result = DbCacheUtility::queryOne($cmd, self::getCacheDuration());
        if ($result != false) {
            //图片列表
            $result['goods_image_list'] = array_merge(self::getGoodsTitleImage($goodsCode), self::getGoodsImageList($goodsCode));
            //同组商品 group_code
            $result['group_goods_list'] = self::getGroupGoodsList($institutionCode, $result['group_code']);
            return $result;
        } else {
            return false;
        }
    }

    /**获得产品图片列表
     * @param $goodsCode
     * @return mixed
     */
    public static function getGoodsImageList($goodsCode)
    {
        $cmd = Yii::$app->db->createCommand('SELECT * FROM t_goods_image WHERE goods_code=:goodsCode AND type=2');
        $cmd->bindValue(':goodsCode', $goodsCode);
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }

    /**获得产品图片列表
     * @param $goodsCode
     * @return mixed
     */
    public static function getGoodsTitleImage($goodsCode)
    {
        $cmd = Yii::$app->db->createCommand('SELECT * FROM t_goods_image WHERE goods_code=:goodsCode AND type=1');
        $cmd->bindValue(':goodsCode', $goodsCode);
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }

    /**获得产品组信息
     * @param $institutionCode
     * @param $group_code
     * @return mixed
     */
    public static function getGroupGoodsList($institutionCode, $group_code)
    {
        $cmd = Yii::$app->db->createCommand('SELECT code,group_item_name FROM t_goods g
        WHERE group_code=:group_code and institution_code=:institution_code and
         g.is_enabled=1 and g.is_shelved=1 and g.is_show=1');
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':group_code', $group_code);
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }

    /**
     * 前端商城获取商品库存
     * @param string $goodsCode
     * @return mixed
     */
    public static function getGoodsStock($goodsCode)
    {
        return intval(Inteface::getGoodsStock($goodsCode));
    }

    /**
     * 选择商品添加到购物车
     * @return string
     * @throws \Exception
     */
    public static function addCart($userId, $goodsCode, $quantity = 1)
    {
        $cmd = \Yii::$app->db->createCommand('CALL p_add_cart(:in_user_id, :in_goods_code, :in_quantity)');
        $cmd->bindValue(':in_user_id', $userId);
        $cmd->bindValue(':in_goods_code', $goodsCode);
        $cmd->bindValue(':in_quantity', $quantity);

        $cartId = $cmd->queryScalar();
        return $cartId;
    }

    public static function getCartGoodsCount($userId)
    {
        $cmd = \Yii::$app->db->createCommand('select IFNULL(sum(t0.quantity),0) from t_cart t0 inner join t_goods t1 on t1.code=t0.goods_code and t1.is_shelved=1 inner join t_user t2 on t2.institution_code=t1.institution_code and t0.user_id=t2.id where t0.user_id=:userId');
        $cmd->bindValue(':userId', $userId);
        return $cmd->queryScalar();
    }

    /**获取用户购物车
     * @param $userId
     * @return array
     */
    public static function getCart($userId)
    {
        $cmd = \Yii::$app->db->createCommand('
            SELECT t0.id, t0.goods_code, t0.quantity, t0.is_selected,t2.is_crossborder, t2.`name`, t2.selling_price,t2.selling_price*t3.integral_rate AS selling_integral,t2.selling_price*t3.integral_rate*t0.quantity AS selling_integral_sum,t4.image_url AS goods_image FROM t_cart t0
            LEFT JOIN t_user t1 ON t1.id=t0.user_id
            LEFT JOIN t_goods t2 ON t2.institution_code=t1.institution_code AND t2.code=t0.goods_code
            LEFT JOIN t_institution t3 ON t3.`code`=t1.institution_code
            LEFT JOIN t_goods_image t4 ON t4.goods_code=t2.`code` AND t4.type=1
            WHERE t2.is_enabled=1 AND t2.is_shelved=1 AND t2.is_show=1 AND t0.user_id=:user_id');
        $cmd->bindValue(':user_id', $userId);

        $items = $cmd->queryAll();
        return $items;
    }

    /**设置购物车商品选择状态
     * @param $userId
     * @param $cartId
     * @param $isSelected
     * @throws \yii\db\Exception
     */
    public static function setCartSelected($userId, $cartId, $isSelected)
    {
        $cmd = \Yii::$app->db->createCommand('CALL p_set_cart_selected(:in_user_id, :in_cart_id, :in_is_selected)');
        $cmd->bindValue(':in_user_id', $userId);
        $cmd->bindValue(':in_cart_id', $cartId);
        $cmd->bindValue(':in_is_selected', $isSelected);

        $cmd->execute();
    }

    /**移除某用户购物车中的某件商品
     * @param $userId
     * @param $cartId
     * @throws \yii\db\Exception
     */
    public static function removeCart($userId, $cartId)
    {
        $cmd = \Yii::$app->db->createCommand('CALL p_remove_cart(:in_user_id, :in_cart_id)');
        $cmd->bindValue(':in_user_id', $userId);
        $cmd->bindValue(':in_cart_id', $cartId);

        $cmd->execute();
    }

    /**修改某用户购物车中商品数
     * @param $userId
     * @param $cartId
     * @param $quantity
     * @throws \yii\db\Exception
     */
    public static function setCartQuantity($userId, $cartId, $quantity)
    {
        $cmd = \Yii::$app->db->createCommand('CALL p_set_cart_quantity(:in_user_id, :in_cart_id, :in_quantity)');
        $cmd->bindValue(':in_user_id', $userId);
        $cmd->bindValue(':in_cart_id', $cartId);
        $cmd->bindValue(':in_quantity', $quantity);

        $cmd->execute();
    }

    /**获取购物车选定的产品列表
     * @param $userId
     * @return array
     */
    public static function getCartSelected($userId)
    {
        $cmd = \Yii::$app->db->createCommand('
            SELECT t0.id, t0.goods_code, t0.quantity, t0.is_selected, t2.`name`,t2.`is_crossborder`, t2.selling_price,t2.selling_price*t3.integral_rate AS selling_integral,t2.selling_price*t3.integral_rate*t0.quantity AS selling_integral_sum,t4.image_url AS goods_image FROM t_cart t0
            LEFT JOIN t_user t1 ON t1.id=t0.user_id
            LEFT JOIN t_goods t2 ON t2.institution_code=t1.institution_code AND t2.code=t0.goods_code
            LEFT JOIN t_institution t3 ON t3.`code`=t1.institution_code
            LEFT JOIN t_goods_image t4 ON t4.goods_code=t2.`code` AND t4.type=1
            WHERE t2.is_enabled=1 AND t2.is_shelved=1 AND t2.is_show=1 AND t0.is_selected=1 AND t0.user_id=:user_id');
        $cmd->bindValue(':user_id', $userId);

        $items = $cmd->queryAll();
        return $items;
    }

    /**获得默认地址
     * @param $userId
     * @return array|false|null
     */
    public static function getDefaultAddress($userId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_address WHERE is_enabled=1 AND is_default=1 AND user_id=:user_id');
        $cmd->bindValue(':user_id', $userId);

        return $cmd->queryOne();
    }

    /**
     * 根据订单ID获取订单产品详情,
     * @param int $orderCode
     * @return array|false
     */
    public static function getOrderGoodsDetail($orderId, $institutionCode)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT t1.*,t1.selling_price*t3.integral_rate AS selling_integral,t1.selling_price*t3.integral_rate*t1.quantity AS selling_integral_sum,t4.image_url
             FROM (select og.order_id,og.goods_code,og.quantity,og.selling_price,og.goods_name,g.model,g.format,g.is_crossborder
				 from t_order_goods og left join t_goods g on og.goods_code=g.code and g.institution_code=:institution_code )  t1
            LEFT JOIN t_order t2 ON t2.id=t1.order_id
            LEFT JOIN t_institution t3 ON t3.`code`=t2.institution_code
            LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.goods_code AND t4.type=1
            WHERE t1.order_id=:order_id ');
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':order_id', $orderId);
//        echo $cmd->getRawSql();exit;
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }

    /**获取物流信息
     * @param $orderCode
     * @return array
     */
    public static function getOrderDelivery($orderCode)
    {
        $sql="select t0.* from t_order_delivery t0
        where t0.order_code=:orderCode order by t0.create_datetime";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':orderCode', $orderCode);
        return $cmd->queryAll();
    }
    /**获取物流信息
     * @param $orderCode
     * @return array
     */
    public static function getOrderDeliveryDetailGoodsList($orderDeliveryId)
    {
        $sql=" SELECT t1.*,t4.image_url from t_order_delivery_goods t1
                LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.goods_code AND t4.type=1
                WHERE t1.order_delivery_id=:orderDeliveryId";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':orderDeliveryId', $orderDeliveryId);
        return $cmd->queryAll();
    }

    /**获取机构支付方式 1 混合支付
     * @param $institutionCode
     * @return false|null|string
     */
    public static function getInstitution_is_cash_order($institutionCode)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT is_cash_order FROM t_institution WHERE code=:institutionCode');
        $cmd->bindValue(':institutionCode', $institutionCode);
        return $cmd->queryScalar();
    }

    /**子过程--生成订单
     * @param $userId
     * @param $remark
     * @return array
     * @throws \yii\db\Exception
     */
    public static function sub_createOrder($userId, $remark, $idcard_name = '', $idcard_number = '')
    {
        $status = 1;
        $orderId = null;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_add_order_with_user(:in_user_id, :in_remark,:in_idcard_name,:in_idcard_number, @out_order_id)');
            $cmd->bindValue(':in_user_id', $userId);
            $cmd->bindValue(':in_remark', $remark);
            $cmd->bindValue(':in_idcard_name', $idcard_name);
            $cmd->bindValue(':in_idcard_number', $idcard_number);

            $status = $cmd->queryScalar();

            $cmd = \Yii::$app->db->createCommand('SELECT @out_order_id');
            $orderId = $cmd->queryScalar();

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }


        return ['status' => $status, 'orderId' => $orderId];
    }

    /**
     * 纯积分支付--生成并支付订单
     */
    public static function submitOrder($userId, $remark, $idcard_name, $idcard_number)
    {

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            //判断用户积分是否可以支付
            $cards = self::getCartSelected($userId);
            $order_integral = 0;
            foreach ($cards as $item) {
                $order_integral = $order_integral + intval($item['selling_integral_sum']);
            }
            $userIntegral = User::getUserIntegral($userId);
            if ($order_integral > $userIntegral) {
                return ['cash_integral' => $order_integral - $userIntegral, 'status' => 20, 'message' => '用户积分不足'];
            }
            //生成订单
            $re = self::sub_createOrder($userId, $remark, $idcard_name, $idcard_number);
            $status = $re['status'];
            $orderId = $re['orderId'];
            if ($status == 0) {
                //订单支付  0:支付成功; 1:失败(系统错误) 10:订单状态不对,不能进行支付; 20:用户积分不足,支付失败
                $payStatus = Order::pay($orderId);
                if ($payStatus == '0') {
                    $transaction->commit();
                    return ['status' => 0, 'orderId' => $orderId];
                }
                if ($payStatus == '1') {
                    return ['status' => 1, 'message' => '失败(系统错误)'];
                }
                if ($payStatus == '10') {
                    return ['status' => 10, 'message' => '订单状态不对,不能进行支付'];
                }
                if ($payStatus == '20') {
                    return ['status' => 20, 'message' => '用户积分不足'];
                }

            } else {
                return ['status' => 1, 'message' => '订单生成失败'];
            }

        } catch (\Exception $e) {
            $transaction->rollBack();
            return ['status' => 1, 'message' => $e->getMessage()];
        }


    }

    /**
     * 只生成订单
     */
    public static function createOrder($userId, $remark, $idcard_name, $idcard_number)
    {
        $re = self::sub_createOrder($userId, $remark, $idcard_name, $idcard_number);
        $status = $re['status'];
        $orderId = $re['orderId'];
        if ($status == 0) {
            //获取订单
            $order = self::getOrderById($orderId);
            return ['status' => 0, 'orderId' => $orderId, 'orderCode' => $order['code']];
        } else {
            return ['status' => 1, 'message' => '订单生成失败'];
        }
    }
    public static function getInstitutionInfo($institutionCode){
        $sql="select * from t_institution where code=:institutionCode";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institutionCode',$institutionCode);
        return $cmd->queryOne();
    }
    /**
     * 支付订单（不生成订单）
     */
    public static function payOrder($userId, $orderId)
    {
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            //获取订单
            $order = self::getOrderById($orderId);
            if ($order) {
                $integral_rate=self::getInstitutionInfo($order['institution_code']);
                $order_integral = $order['total_selling_price'] * floatval($integral_rate);
                $userIntegral = User::getUserIntegral($userId);
                if ($order_integral > $userIntegral) {
                    return ['cash_integral' => $order_integral - $userIntegral, 'status' => 20, 'message' => '用户积分不足'];
                }
                //订单支付  0:支付成功; 1:失败(系统错误) 10:订单状态不对,不能进行支付; 20:用户积分不足,支付失败
                $payStatus = Order::pay($orderId);
//                return ['status' => 1, 'message' => $transaction->level];
                if ($payStatus == '0') {
                    $transaction->commit();
                    return ['status' => 0, 'orderId' => $orderId, 'message' => ''];
                }
                if ($payStatus == '1') {
                    return ['status' => 1, 'message' => '失败(系统错误)'];
                }
                if ($payStatus == '10') {
                    return ['status' => 10, 'message' => '订单状态不对,不能进行支付'];
                }
                if ($payStatus == '20') {
                    return ['status' => 20, 'message' => '用户积分不足'];
                }

            } else {
                return ['status' => 1, 'message' => '订单不存在'];
            }

        } catch (\Exception $e) {
            $transaction->rollBack();
            return ['status' => 1, 'message' => $e->getMessage()];
        }

    }

    /**
     * 更新未支付订单信息
     * ps:商品不能购买了:quantity数量设为0
     * ps:订单总价为0:订单状态设置为无效
     * @param $orderId
     */
    public static function updateOrder($orderId)
    {

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('select * from t_order where id=:orderId and status=1');
            $cmd->bindValue(':orderId', $orderId);
            $order = $cmd->queryOne();
            if ($order == false) {
                throw new \Exception('订单不存在或者状态不对,不能更新订单信息');
            }
            $institutionCode = $order['institution_code'];
            //更新商品状态  如果不能购买了quantity设置为0
            $sql = "update t_order_goods set quantity=0 where order_id=:orderId and
                    goods_code not in (  select code from t_goods  where
              is_enabled=1 and is_shelved=1 and is_show=1  and institution_code=:institutionCode)";
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':orderId', $orderId);
            $cmd->bindValue(':institutionCode', $institutionCode);
            $cmd->execute();
            //更新商品价格
            $sql = "update t_order_goods og ,t_goods g set og.selling_price = g.selling_price where
            og.order_id=:orderId and g.code=og.goods_code and g.institution_code=:institutionCode";
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':orderId', $orderId);
            $cmd->bindValue(':institutionCode', $institutionCode);
            $cmd->execute();
            //合计价格
            $cmd = \Yii::$app->db->createCommand('select
                sum(t1.selling_price * t3.integral_rate * t0.quantity) as total_integral
                , sum(t1.selling_price * t0.quantity) as total_selling_price  from
                    t_order_goods t0,t_goods t1 left join t_institution t3 on t3.code=t1.institution_code
                    where t0.goods_code=t1.code and t1.institution_code=:institutionCode and  t0.order_id=:orderId');
            $cmd->bindValue(':orderId', $orderId);
            $cmd->bindValue(':institutionCode', $institutionCode);
            $result = $cmd->queryOne();
            $total_selling_price = $result['total_selling_price'];
            if ($total_selling_price <= 0) {//设置订单状态 32已失效（Invalid）
                $cmd = \Yii::$app->db->createCommand('update t_order set status=:status where id=:orderId');
                $cmd->bindValue(':orderId', $orderId);
                $cmd->bindValue(':status', 32);
                $cmd->execute();
            } else {
                $cmd = \Yii::$app->db->createCommand('update t_order set total_selling_price=:total_selling_price where id=:orderId');
                $cmd->bindValue(':orderId', $orderId);
                $cmd->bindValue(':total_selling_price', $total_selling_price);
                $cmd->execute();
            }
            $transaction->commit();
            return 0;
        } catch (\Exception $e) {
            $transaction->rollBack();
        }
        return 1;

    }

    /**
     * 更新订单价格或者订单状态
     * @param $orderId
     */
    public static function updateOrder_total_selling_price($orderId, $total_selling_price)
    {
        if ($total_selling_price <= 0) {//设置订单状态 32已失效（Invalid）
            $cmd = \Yii::$app->db->createCommand('update t_order set status=:status where id=:orderId');
            $cmd->bindValue(':orderId', $orderId);
            $cmd->bindValue(':status', 32);
            $cmd->execute();
        } else {
            $cmd = \Yii::$app->db->createCommand('update t_order set total_selling_price=:total_selling_price where id=:orderId');
            $cmd->bindValue(':orderId', $orderId);
            $cmd->bindValue(':total_selling_price', $total_selling_price);
            $cmd->execute();
        }
    }

    /**获取当前订单最新价格
     * @param $orderId
     * @return array|false
     */
    public static function getNowOrder_total_selling_price($orderId)
    {
        $cmd = \Yii::$app->db->createCommand('select institution_code from t_order where id=:orderId');
        $cmd->bindValue(':orderId', $orderId);
        $institutionCode = $cmd->queryScalar();
        $cmd = \Yii::$app->db->createCommand('select
        sum(t1.selling_price * t3.integral_rate * t0.quantity) as total_integral
        , sum(t1.selling_price * t0.quantity) as total_selling_price  from
            t_order_goods t0,t_goods t1 left join t_institution t3 on t3.code=t1.institution_code
            where t0.goods_code=t1.code and t1.institution_code=:institutionCode and  t0.order_id=:orderId');
        $cmd->bindValue(':orderId', $orderId);
        $cmd->bindValue(':institutionCode', $institutionCode);
        $result = $cmd->queryOne();
        return $result;
    }

    //获取订单
    public static function getOrderById($orderId)
    {
        $cmd = \Yii::$app->db->createCommand('select * from t_order where id=:orderId');
        $cmd->bindValue(':orderId', $orderId);

        return $cmd->queryOne();
    }

    //获取订单
    public static function getOrderByCode($orderCode)
    {
        $cmd = \Yii::$app->db->createCommand('select * from t_order where  code=:orderCode');
        $cmd->bindValue(':orderCode', $orderCode);

        return $cmd->queryOne();
    }

    //获取订单
    public static function getOrderByCode_detail($orderCode)
    {
        $cmd = \Yii::$app->db->createCommand('select o.*,c.integral as cash_integral,c.total_fee as cash_total_fee from t_order o left join t_order_cash_payment_record  c on c.order_code=o.code and c.status=1
             where o.code=:orderCode');
        $cmd->bindValue(':orderCode', $orderCode);

        return $cmd->queryOne();
    }

    /*1：未支付（WaitingPay）
    2：支付中（Paying）【积分已经扣除，等待现金扣除】
    3：支付失败
    9：已支付（HasPaid）
    10：已提交给机构（已进入流转订单）
    ---12：待审核订单（需要人工审核时还未审核）
    ---13：未结算订单（当前机构余额不足）
    11：待确认订单（等待上级机构完成此订单的流转）
    20：订单已同步（订单已经到业务系统）
    21：出库中（WaitingPacking）
    22：配送中（Express）
    23：配送完成（ExpressFinish）
    30：已完成（Completed）
    31：已取消（Canceled）
    32：已失效（Invalid）
    33：订单已拆分*/

    public static function orderStatusToUserTxt($status)
    {
        if ($status == '1') {
            return "未支付";
        }
        if ($status == '10' || $status == '11') {
            return "已支付待确认";
        }
        if ($status == '20') {
            return "待出库";
        }
        if ($status == '21') {
            return "出库中";
        }
        if ($status == '22') {
            return "配送中";
        }
        if ($status == '23') {
            return "配送完成";
        }

        if ($status == '30') {
            return "已完成";
        }
        if ($status == '31') {
            return "已取消";
        }
        if ($status == '32') {
            return "已失效";
        }
        return "未知状态";
    }

    public static function getActivityBargainList($institutionCode)
    {
        $cmd = \Yii::$app->db->createCommand('select * from t_activity_bargain where institution_code=:institutionCode and is_enabled=1 and is_deleted=0 ');
        $cmd->bindValue(':institutionCode', $institutionCode);

        return $cmd->queryAll();
    }


    /**
     * 获取某机构所有置于首页的特价商品列表
     * @param $institutionCode
     * @return array
     */
    public static function getBargainTopGoodsListByInstitution($institutionCode)
    {
        $cmd = Yii::$app->db->createCommand('
            SELECT t1.goods_code,t1.goods_name,t1.is_crossborder,t1.old_price*t5.integral_rate as old_integral,t1.selling_price*t5.integral_rate as selling_integral, t2.image_url FROM t_activity_bargain t0
            INNER JOIN (select bg.*,g.name goods_name,g.is_crossborder,g.selling_price from t_activity_bargain_goods bg,t_goods g
        where bg.institution_code=:institutionCode and g.institution_code=:institutionCode  and bg.goods_code=g.code
        and is_shelved=1 and g.is_show =1 and g.is_enabled=1) t1 ON t1.activity_id=t0.id
            left JOIN t_goods_image t2 ON t2.goods_code=t1.goods_code and t2.type=1
            left join t_institution t5 on t5.`code`=:institutionCode
            WHERE
            t0.is_enabled=1 AND
            t0.is_deleted=0 AND
            t0.start_datetime<NOW() AND
            t0.end_datetime>NOW() AND
            t1.is_top=1 AND
            t0.institution_code=:institutionCode');
        $cmd->bindValue(':institutionCode', $institutionCode);
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }

    /**获取用户积分记录
     * @param $userId
     * @param bool $getCount
     * @param int $page
     * @param int $pageSize
     * @return array|false|null|string
     */
    public static function getUserIntegralRecord($userId, $getCount = true, $page = 1, $pageSize = 20)
    {
        $csql = "select * from t_user_integral_record where user_id=:user_id";
        $sql = '';
        if ($getCount) {
            $sql = "select count(1) from ( $csql ) t99";
        } else {
            $sql = "select * from ( $csql ) t99 ORDER by create_datetime desc LIMIT :offset, :limit";
        }
        $cmd = \Yii::$app->db->createCommand($sql);

        if ($getCount == false) {
            $page = $page < 1 ? 1 : $page;
            $offset = ($page - 1) * $pageSize;
            $cmd->bindValue(':offset', $offset);
            $cmd->bindValue(':limit', $pageSize);
        }

        $cmd->bindValue(':user_id', $userId);
        if ($getCount) {
            return $cmd->queryScalar();
        } else {
            return $cmd->queryAll();
        }
    }

    /**获取机构福利信息
     * @param $id
     * @return array|false
     */
    public static function getInstitutionWalfare($id)
    {
        $sql = "select * from t_institution_walfare where id=:id";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        return $cmd->queryOne();
    }

//1：发放福利积分(有子类型)
//2：兑换商品消费积分
//3：订单取消返还的积分
//4：积分转增减少的积分
//5：积分转增获得的积分
//6：订单现金支付增加的积分
//7：扫描礼卡增加的积分
//8：玩游戏消费积分
//9：抽奖消费积分
//10：订单退货返回的积分
//用户积分来源名称转换
    public static function caseUserIntegralRecordTypeAndRemark($record_type, $origin_id)
    {
        $remark = '';
        switch ($record_type) {
            case '1':
                // 0：调整;
                //1：福利;
                //2：奖励;
                $type = '调整';
                if ($record_type == '1')
                    $type = '发放福利';
                if ($record_type == '2')
                    $type = '发放奖励';
                $remark = '未找到记录';
                $walfare = self::getInstitutionWalfare($origin_id);
                if ($walfare) {
                    $remark = $walfare['walfare_name'];
                }
                break;
            case '2':
                $type = '兑换商品消费';
                break;
            case '3':
                $type = '订单取消返还';
                break;
            case '4':
                $type = '积分转增减少';
                break;
            case '5':
                $type = '积分转增获得';
                break;
            case '6':
                $type = '订单现金支付增加';
                break;
            case '7':
                $type = '扫描礼卡增加';
                break;
            case '8':
                $type = '玩游戏消费';
                break;
            case '9':
                $type = '抽奖消费';
                break;
            case '10':
                $type = '订单退货返还';
                break;
            default:
                $type = '未知类型';
        }
        return [$type, $remark];
    }

    static public function pageDo($dataCount, $page, $pageSize)
    {
        if ($dataCount % $pageSize > 0) {
            $maxPage = intval($dataCount / $pageSize) + 1;
        } else {
            $maxPage = intval($dataCount / $pageSize);
        }
        if ($page > $maxPage) {
            $page = $maxPage;
        }
        return [$page, $maxPage];
    }

    /**查询首页扩展分类显示的产品列表
     * @param $institutionCode
     * @param $identifier_code
     * @return mixed
     */
    static public function getIndexExtendCategoryIndexShowGoodsList($institutionCode, $extend_category_id)
    {
        $sql = "select t1.`code` goods_code, t1.institution_code, t1.`name`,t4.image_url AS goods_image ,t1.selling_price*t3.integral_rate AS selling_integral
         from (select *,IFNULL(group_code,code) as ext_group_code from t_goods
              WHERE  institution_code=:institution_code  AND is_enabled=1  AND is_shelved=1 AND is_show=1  ) t1
          LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.`code` AND t4.type=1
           LEFT JOIN t_institution t3 ON t3.`code`=t1.institution_code
        inner  JOIN (select * from t_goods_extend_category_goods where  extend_category_id=:extend_category_id AND is_index_show=1
        ) t5 ON  t5.goods_code=t1.code
          WHERE 1=1 group by t1.ext_group_code ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':extend_category_id', $extend_category_id);
        return DbCacheUtility::queryAll($cmd, self::getCacheDuration());
    }

    /**
     * 校验购物车产品
     * @param $in_user_id
     * @return array
     */
    static public function checkCartGoods($user_id){
        //积分小于0的购物车产品
        $sql="SELECT count(1) FROM t_cart t0
            LEFT JOIN t_user t1 ON t1.id=t0.user_id
            LEFT JOIN t_goods t2 ON t2.`code`=t0.goods_code AND t2.institution_code=t1.institution_code
            WHERE  t0.user_id=:in_user_id AND t0.is_selected=1 and IFNULL( t2.selling_price,0)<=0";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_user_id', $user_id);
        return  $cmd->queryScalar();
       
    }

    /**检查此订单是否是此用户的
     * @param $user_id
     * @param $orderCode
     * @return bool
     */
    static public function checkOrderOwner($user_id,$orderCode){
        $sql='select count(1) from t_order where code=:orderCode and user_id=:userId';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':userId', $user_id);
        $cmd->bindValue(':orderCode', $orderCode);
        return $cmd->queryScalar()>0;
    }
    /**
     * 获取用户所有收货地址列表
     * @param $userId
     * @return array|null
     */
    public static function getUserAllAddress($userId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_address WHERE is_enabled=1 AND user_id=:user_id ORDER BY is_default desc,create_datetime DESC');
        $cmd->bindValue(':user_id', $userId);

        $items = $cmd->queryAll();
        return $items;
    }

    /**
     * 查询商品扩展分类列表  百胜用
     * @param string $institution_code
     * @return array|null
     */
    public static function getGoodsExtendCategoryList_bs($institution_code)
    {
        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "select * from t_goods_extend_category where institution_code=:institution_code and identifier_code like ('pc-%') order by  modify_datetime desc";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = DbCacheUtility::queryAll($cmd, self::getCacheDuration());
            return $result;
        }

        return $result;
    }
}
